/*
	EXEC SP_UPDATE_PRODUCT_COST_CODE
*/
CREATE PROC SP_UPDATE_PRODUCT_COST_CODE
AS
BEGIN
	
	DECLARE @PROCESS_TYPE_CURSOR CURSOR
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @PROCESSSTEP_ID BIGINT
	DECLARE @FG_PROCESSSTEP_ID BIGINT
	DECLARE @FG_COST_CODE VARCHAR(10)	
	DECLARE @COST_CODE VARCHAR(10)

	SET @PROCESS_TYPE_CURSOR = CURSOR FOR
	SELECT PS_TYPE, P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
	AND P.COST_CODE IS NULL 
	--AND PS_TYPE IN ( 'FAB', 'WAFER BANK', 'FAB OPTION', 'DIE BANK' )
	--AND P_PARENT_PRODUCT_ID = 6
	ORDER BY ( CASE WHEN PS_TYPE = 'FAB' THEN 1 WHEN PS_TYPE = 'WAFER BANK' THEN 2 WHEN PS_TYPE = 'FAB OPTION' THEN 3 WHEN PS_TYPE = 'DIE BANK' THEN 4 WHEN PS_TYPE = 'ASSEMBLY' THEN 5 WHEN PS_TYPE = 'FT' THEN 6 WHEN PS_TYPE = 'FPO' THEN 7 WHEN PS_TYPE = 'TAPE & REEL' THEN 8 WHEN PS_TYPE = 'FG' THEN 9 WHEN PS_TYPE = 'FINISHED GOODS' THEN 10 END )

	OPEN @PROCESS_TYPE_CURSOR	
  	FETCH NEXT FROM @PROCESS_TYPE_CURSOR INTO @PS_TYPE, @PROCESSSTEP_ID

 	WHILE @@FETCH_STATUS = 0
 	BEGIN
		IF( @PS_TYPE = 'FAB' OR @PS_TYPE = 'WAFER BANK' OR @PS_TYPE = 'FAB OPTION' OR @PS_TYPE = 'DIE BANK' )
		BEGIN
			SELECT TOP 1 @FG_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @PROCESSSTEP_ID, 1) WHERE PS_TYPE = 'FG'

			SELECT @FG_COST_CODE = COST_CODE FROM PRODUCT WHERE PROCESSSTEP_ID = @FG_PROCESSSTEP_ID AND COST_CODE IS NOT NULL

			IF( @FG_COST_CODE IS NULL )
			BEGIN
				SELECT @FG_COST_CODE = COST_CODE FROM FN_CALCULATE_PRODUCT_COST_CODE( @FG_PROCESSSTEP_ID, 'FG' )
			END			
			
			IF( @FG_COST_CODE IS NOT NULL )
			BEGIN
				UPDATE PRODUCT SET COST_CODE = @FG_COST_CODE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			END
		END
		ELSE IF( @PS_TYPE = 'ASSEMBLY' OR @PS_TYPE = 'FT' OR @PS_TYPE = 'FPO' OR @PS_TYPE = 'FG' OR @PS_TYPE = 'TAPE & REEL' OR @PS_TYPE = 'FINISHED GOODS')
		BEGIN
			SELECT @COST_CODE = COST_CODE FROM FN_CALCULATE_PRODUCT_COST_CODE( @PROCESSSTEP_ID, 'ASSEMBLY' )	

			IF( @COST_CODE IS NOT NULL )
			BEGIN
				UPDATE PRODUCT SET COST_CODE = @COST_CODE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
			END
		END

 		FETCH NEXT FROM @PROCESS_TYPE_CURSOR INTO @PS_TYPE, @PROCESSSTEP_ID
 	END

 	CLOSE @PROCESS_TYPE_CURSOR
	DEALLOCATE @PROCESS_TYPE_CURSOR
END
